<?php
/*
 * @Author: your name
 * @Date: 2021-11-03 15:16:15
 * @LastEditTime: 2021-12-22 16:40:52
 * @LastEditors: Please set LastEditors
 * @Description: 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
 * @FilePath: \property\app\Models\admin_model\Column.php
 */

namespace App\Models\admin_model;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class FindShopCollectionHistory extends Model
{
    use HasFactory;
    static string $tableName = 'find_shop_collection_history';

    public static function  collectionHistoryAdd($formData){
         return DB::table(self::$tableName)->insert($formData);
    }

    public static function historyDel($user_openid,$concatStr){
        // return DB::table(self::$tableName)->del();
        // return DB::table(self::);
        // echo ' DELETE * FROM  '.self::$tableName.' WHERE `id` = 3 ';
        // echo ' DELETE  FROM  '.self::$tableName.' WHERE  `type` = 1 AND `user_openid` = "'.$user_openid.'"   AND    CONCAT( `table` ,"_",`target_id`) IN ('.$concatStr.') ';
        return DB::delete(' DELETE  FROM  '.self::$tableName.' WHERE  `type` = 1 AND `user_openid` = "'.$user_openid.'"   AND    CONCAT( `table` ,"_",`target_id`) IN ('.$concatStr.') ');
    }
    public static function historyDelOver($openid,$maxNum = 30){
        return DB::delete(' DELETE FROM  `find_shop_collection_history` WHERE  `id` < ( select id from ( SELECT `id` FROM `find_shop_collection_history` WHERE `type` = 1 AND `user_openid` = "'.$openid.'"   ORDER BY `id` DESC LIMIT '.$maxNum.',1 ) t )  AND `type` = 1 AND `user_openid` = "'.$openid.'" ');
    }


    public static function  storeHousingHistory($housingIdStr,$storeIdStr,$buildingIdStr,$orderByStr,$limitStr = ''){
        // echo 'SELECT `all`.*, `areaCommunity`.`name` AS `community_name`, `areaStreet`.`name` AS `street_name` FROM ( SELECT "housing" AS `type`, CONCAT("housing_",`housing`.`id`) as `table_id`, `housing`.`delete_time`,`housing`.`shelves`,`housing`.`cover_image`, `housing`.`id`, `housing`.`top_time`, `housing`.`sort`, `housing`.`create_time`, `building`.`community_id`, `building`.`street_id`, `housing`.`meters_money`, `housing`.`level_des`, `building`.`title`, `housing`.`spaceNum`, `housing`.`tagList` FROM `housing` LEFT JOIN `tower_building` AS `building` ON `building`.`id` = `housing`.`building_id` WHERE `housing`.`id` IN ('.$housingIdStr.') UNION ALL SELECT "store" AS `type`, CONCAT("store_",`store`.`id`) as `table_id`,`store`.`delete_time`,`store`.`shelves` ,`store`.`cover_image`, `store`.`id`, `store`.`top_time`, `store`.`sort`, `store`.`create_time`, `store`.`community_id`, `store`.`street_id`, `store`.`meters_money`, `store`.`level_des`, `store`.`title`, `store`.`spaceNum`, `store`.`tagList` FROM `store` WHERE `store`.`id` IN ('.$storeIdStr.') ) AS `all` LEFT JOIN `area` AS `areaStreet` ON `areaStreet`.`id` = `all`.`street_id` LEFT JOIN `area` AS `areaCommunity` ON `areaCommunity`.`id` = `all`.`community_id` ORDER BY  FIELD(`all`.`table_id`, '.$orderByStr.')  '.$limitStr;
        // echo 'SELECT `all`.*, `areaCommunity`.`name` AS `community_name`, `areaStreet`.`name` AS `street_name` FROM ( SELECT "housing" AS `type`, CONCAT("housing_",`housing`.`id`) as `table_id`, `housing`.`delete_time`,`housing`.`shelves`,`housing`.`cover_image`, `housing`.`id`, `housing`.`top_time`, `housing`.`sort`, `housing`.`create_time`, `building`.`community_id`, `building`.`street_id`, `housing`.`meters_money`, `housing`.`level_des`, `building`.`title`, `housing`.`spaceNum`, `housing`.`tagList` FROM `housing` LEFT JOIN `tower_building` AS `building` ON `building`.`id` = `housing`.`building_id` WHERE `housing`.`id` IN ('.$housingIdStr.') UNION ALL SELECT "store" AS `type`, CONCAT("store_",`store`.`id`) as `table_id`,`store`.`delete_time`,`store`.`shelves` ,`store`.`cover_image`, `store`.`id`, `store`.`top_time`, `store`.`sort`, `store`.`create_time`, `store`.`community_id`, `store`.`street_id`, `store`.`meters_money`, `store`.`level_des`, `store`.`title`, `store`.`spaceNum`, `store`.`tagList` FROM `store` WHERE `store`.`id` IN ('.$storeIdStr.') ) AS `all` LEFT JOIN `area` AS `areaStreet` ON `areaStreet`.`id` = `all`.`street_id` LEFT JOIN `area` AS `areaCommunity` ON `areaCommunity`.`id` = `all`.`community_id` ORDER BY  FIELD(`all`.`table_id`, '.$orderByStr.')  '.$limitStr;
        
        return Db::select(' SELECT `all`.*, `areaCommunity`.`name` AS `community_name`, `areaStreet`.`name` AS `street_name` FROM ( SELECT "housing" AS `type`, CONCAT( "housing_", `housing`.`id` ) AS `table_id`, `housing`.`delete_time`, `housing`.`shelves`, `housing`.`cover_image`, `housing`.`id`, `housing`.`top_time`, `housing`.`sort`, `housing`.`create_time`, `building`.`community_id`, `building`.`street_id`, `housing`.`meters_money`, `housing`.`level_des`, `building`.`title`, `housing`.`spaceNum`, `housing`.`tagList` FROM `housing` LEFT JOIN `tower_building` AS `building` ON `building`.`id` = `housing`.`building_id` WHERE `housing`.`id` IN ( '.$housingIdStr.' ) UNION ALL SELECT "store" AS `type`, CONCAT( "store_", `store`.`id` ) AS `table_id`, `store`.`delete_time`, `store`.`shelves`, `store`.`cover_image`, `store`.`id`, `store`.`top_time`, `store`.`sort`, `store`.`create_time`, `store`.`community_id`, `store`.`street_id`, `store`.`meters_money`, `store`.`level_des`, `store`.`title`, `store`.`spaceNum`, `store`.`tagList` FROM `store` WHERE `store`.`id` IN ('.$storeIdStr.' ) UNION ALL SELECT "building" AS `type`, CONCAT( "building_", `building`.`id` ) AS `table_id`, `building`.`delete_time`, `building`.`shelves`, `building`.`cover_image`, `building`.`id`, `building`.`top_time`, `building`.`sort`, `building`.`create_time`, `building`.`community_id`, `building`.`street_id`, IFNULL(CONCAT( MIN(`housing`.`meters_money`),"~",MAX(`housing`.`meters_money`)),0)   as `meters_money`, "" as `level_des`, `building`.`title`, IFNULL(CONCAT(MIN(`housing`.`spaceNum`),"~",MAX(`housing`.`spaceNum`)),0) as `spaceNum`, "" as `tagList` FROM `tower_building` as `building` LEFT JOIN `housing` ON `housing`.`building_id` = `building`.`id` AND `housing`.`delete_time` is null AND `housing`.`shelves` = 1 WHERE `building`.`id` IN ('.$buildingIdStr.'  )  GROUP BY `building`.`id` ) AS `all` LEFT JOIN `area` AS `areaStreet` ON `areaStreet`.`id` = `all`.`street_id` LEFT JOIN `area` AS `areaCommunity` ON `areaCommunity`.`id` = `all`.`community_id` ORDER BY FIELD( `all`.`table_id`, '.$orderByStr.' ) '.$limitStr);
       
        // return Db::select('SELECT `all`.*, `areaCommunity`.`name` AS `community_name`, `areaStreet`.`name` AS `street_name` FROM ( SELECT "housing" AS `type`, CONCAT("housing_",`housing`.`id`) as `table_id`, `housing`.`delete_time`,`housing`.`shelves`,`housing`.`cover_image`, `housing`.`id`, `housing`.`top_time`, `housing`.`sort`, `housing`.`create_time`, `building`.`community_id`, `building`.`street_id`, `housing`.`meters_money`, `housing`.`level_des`, `building`.`title`, `housing`.`spaceNum`, `housing`.`tagList` FROM `housing` LEFT JOIN `tower_building` AS `building` ON `building`.`id` = `housing`.`building_id` WHERE `housing`.`id` IN ('.$housingIdStr.') UNION ALL SELECT "store" AS `type`, CONCAT("store_",`store`.`id`) as `table_id`,`store`.`delete_time`,`store`.`shelves` ,`store`.`cover_image`, `store`.`id`, `store`.`top_time`, `store`.`sort`, `store`.`create_time`, `store`.`community_id`, `store`.`street_id`, `store`.`meters_money`, `store`.`level_des`, `store`.`title`, `store`.`spaceNum`, `store`.`tagList` FROM `store` WHERE `store`.`id` IN ('.$storeIdStr.') ) AS `all` LEFT JOIN `area` AS `areaStreet` ON `areaStreet`.`id` = `all`.`street_id` LEFT JOIN `area` AS `areaCommunity` ON `areaCommunity`.`id` = `all`.`community_id` ORDER BY  FIELD(`all`.`table_id`, '.$orderByStr.')  '.$limitStr);
    }
    public static function   storeHistory($storeIdStr,$limitStr = ''){
         return DB::select(' SELECT  "store" AS `type`,`store`.`delete_time`,`store`.`shelves`,`areaCommunity`.`name` AS `community_name`, `areaStreet`.`name` AS `street_name`, `store`.`cover_image`, `store`.`id`, `store`.`top_time`, `store`.`sort`, `store`.`create_time`, `store`.`community_id`, `store`.`street_id`, `store`.`meters_money`, `store`.`level_des`, `store`.`title`, `store`.`spaceNum`, `store`.`tagList` FROM `store` LEFT JOIN `area` AS `areaStreet` ON `areaStreet`.`id` = `store`.`street_id` LEFT JOIN `area` AS `areaCommunity` ON `areaCommunity`.`id` = `store`.`community_id` WHERE `store`.`id` in ('.$storeIdStr.') ORDER BY  FIELD(`store`.`id`, '.$storeIdStr.') '.$limitStr);
    }

    public static function   housingHistory($housingIdStr,$limitStr = ''){
        return DB::select('	SELECT "housing" AS `type`,`housing`.`delete_time`,`housing`.`shelves` ,`areaCommunity`.`name` AS `community_name`, `areaStreet`.`name` AS `street_name`, `housing`.`cover_image`, `housing`.`id`, `housing`.`top_time`, `housing`.`sort`, `housing`.`create_time`, `building`.`community_id`, `building`.`street_id`, `housing`.`meters_money`, `housing`.`level_des`, `building`.`title`, `housing`.`spaceNum`, `housing`.`tagList` FROM `housing` LEFT JOIN `tower_building` AS `building` ON `building`.`id` = `housing`.`building_id` LEFT JOIN `area` AS `areaStreet` ON `areaStreet`.`id` = `building`.`street_id` LEFT JOIN `area` AS `areaCommunity` ON `areaCommunity`.`id` = `building`.`community_id` WHERE `housing`.`id` IN ('.$housingIdStr.') ORDER BY  FIELD(`housing`.`id`, '.$housingIdStr.')'.$limitStr);
    }
  

    public static function historyUserList($openid,$limitStr){
     
    return DB::select(' SELECT `storeAndHousing`.*, `areaCommunity`.`name` AS `community_name`, `areaStreet`.`name` AS `street_name`, `history`.`id` AS `history_id` FROM `find_shop_collection_history` AS `history` INNER JOIN ( SELECT "housing" AS `type`, `housing`.`delete_time`, `housing`.`shelves`, `housing`.`cover_image`, `housing`.`id`, `housing`.`top_time`, `housing`.`sort`, `housing`.`create_time`, `building`.`community_id`, `building`.`street_id`, `housing`.`meters_money`, `housing`.`level_des`, `building`.`title`, `housing`.`spaceNum`, `housing`.`tagList` FROM `housing` LEFT JOIN `tower_building` AS `building` ON `building`.`id` = `housing`.`building_id` UNION ALL SELECT "store" AS `type`, `store`.`delete_time`, `store`.`shelves`, `store`.`cover_image`, `store`.`id`, `store`.`top_time`, `store`.`sort`, `store`.`create_time`, `store`.`community_id`, `store`.`street_id`, `store`.`meters_money`, `store`.`level_des`, `store`.`title`, `store`.`spaceNum`, `store`.`tagList` FROM `store` UNION ALL SELECT "building" AS `type`, `building`.`delete_time`, `building`.`shelves`, `building`.`cover_image`, `building`.`id`, `building`.`top_time`, `building`.`sort`, `building`.`create_time`, `building`.`community_id`, `building`.`street_id`, IFNULL(CONCAT( MIN(`housing`.`meters_money`),"-",MAX(`housing`.`meters_money`)),0)   as `meters_money`, "" as `level_des`, `building`.`title`, IFNULL(CONCAT(MIN(`housing`.`spaceNum`),"-",MAX(`housing`.`spaceNum`)),0) as `spaceNum`, "" as `tagList` FROM `tower_building` as `building` LEFT JOIN `housing` ON `housing`.`building_id` = `building`.`id` AND `housing`.`delete_time` is null AND `housing`.`shelves` = 1 GROUP BY `building`.`id` ) AS `storeAndHousing` ON `storeAndHousing`.`type` = `history`.`table` AND `storeAndHousing`.`id` = `history`.`target_id` LEFT JOIN `area` AS `areaStreet` ON `areaStreet`.`id` = `storeAndHousing`.`street_id` LEFT JOIN `area` AS `areaCommunity` ON `areaCommunity`.`id` = `storeAndHousing`.`community_id` WHERE `history`.`user_openid` = "'.$openid.'" AND `history`.`type` = 1 ORDER BY `history_id` DESC '.$limitStr);

    }

    public static function  collectionDel($openid,$table,$targer_id){
        return    DB::delete(' DELETE FROM '.self::$tableName.' WHERE `type` = 2 AND `table` = "'.$table.'" AND `target_id` = "'.$targer_id.'"  AND `user_openid` = "'.$openid.'" ');
    }

    public static function   insertUpdated($formDataStr){
        return   DB::update("INSERT INTO ".self::$tableName." ( ".$formDataStr['keyStr'].") VALUES ( ".$formDataStr['valStr']." ) ON DUPLICATE KEY UPDATE ".$formDataStr['updateStr']);
     }
 
    
    public static function  collectionList($openid,$limitStr){
        // echo ' SELECT `storeAndHousing`.*, `areaCommunity`.`name` AS `community_name`, `areaStreet`.`name` AS `street_name`, `collection`.`id` AS `collection_id` FROM `find_shop_collection_history` AS `collection` INNER JOIN ( SELECT "housing" AS `type`,`housing`.`delete_time`,`housing`.`shelves` ,`housing`.`cover_image`, `housing`.`id`, `housing`.`top_time`, `housing`.`sort`, `housing`.`create_time`, `building`.`community_id`, `building`.`street_id`, `housing`.`meters_money`, `housing`.`level_des`, `building`.`title`, `housing`.`spaceNum`, `housing`.`tagList` FROM `housing` LEFT JOIN `tower_building` AS `building` ON `building`.`id` = `housing`.`building_id` UNION ALL SELECT "store" AS `type`, `store`.`delete_time`,`store`.`shelves`,`store`.`cover_image`, `store`.`id`, `store`.`top_time`, `store`.`sort`, `store`.`create_time`, `store`.`community_id`, `store`.`street_id`, `store`.`meters_money`, `store`.`level_des`, `store`.`title`, `store`.`spaceNum`, `store`.`tagList` FROM `store` ) AS `storeAndHousing` ON `storeAndHousing`.`type` = `collection`.`table` AND `storeAndHousing`.`id` = `collection`.`target_id` LEFT JOIN `area` AS `areaStreet` ON `areaStreet`.`id` = `storeAndHousing`.`street_id` LEFT JOIN `area` AS `areaCommunity` ON `areaCommunity`.`id` = `storeAndHousing`.`community_id` WHERE `collection`.`user_openid` = "'.$openid.'" AND `collection`.`type` = 2 ORDER BY `collection_id` DESC '.$limitStr ;
        return DB::select(' SELECT `storeAndHousing`.*, `areaCommunity`.`name` AS `community_name`, `areaStreet`.`name` AS `street_name`, `history`.`id` AS `history_id` FROM `find_shop_collection_history` AS `history` INNER JOIN ( SELECT "housing" AS `type`, `housing`.`delete_time`, `housing`.`shelves`, `housing`.`cover_image`, `housing`.`id`, `housing`.`top_time`, `housing`.`sort`, `housing`.`create_time`, `building`.`community_id`, `building`.`street_id`, `housing`.`meters_money`, `housing`.`level_des`, `building`.`title`, `housing`.`spaceNum`, `housing`.`tagList` FROM `housing` LEFT JOIN `tower_building` AS `building` ON `building`.`id` = `housing`.`building_id` UNION ALL SELECT "store" AS `type`, `store`.`delete_time`, `store`.`shelves`, `store`.`cover_image`, `store`.`id`, `store`.`top_time`, `store`.`sort`, `store`.`create_time`, `store`.`community_id`, `store`.`street_id`, `store`.`meters_money`, `store`.`level_des`, `store`.`title`, `store`.`spaceNum`, `store`.`tagList` FROM `store` UNION ALL SELECT "building" AS `type`, `building`.`delete_time`, `building`.`shelves`, `building`.`cover_image`, `building`.`id`, `building`.`top_time`, `building`.`sort`, `building`.`create_time`, `building`.`community_id`, `building`.`street_id`, IFNULL(CONCAT( MIN(`housing`.`meters_money`),"-",MAX(`housing`.`meters_money`)),0)   as `meters_money`, "" as `level_des`, `building`.`title`, IFNULL(CONCAT(MIN(`housing`.`spaceNum`),"-",MAX(`housing`.`spaceNum`)),0) as `spaceNum`, "" as `tagList` FROM `tower_building` as `building` LEFT JOIN `housing` ON `housing`.`building_id` = `building`.`id` AND `housing`.`delete_time` is null AND `housing`.`shelves` = 1 GROUP BY `building`.`id` ) AS `storeAndHousing` ON `storeAndHousing`.`type` = `history`.`table` AND `storeAndHousing`.`id` = `history`.`target_id` LEFT JOIN `area` AS `areaStreet` ON `areaStreet`.`id` = `storeAndHousing`.`street_id` LEFT JOIN `area` AS `areaCommunity` ON `areaCommunity`.`id` = `storeAndHousing`.`community_id` WHERE `history`.`user_openid` = "'.$openid.'" AND `history`.`type` =2 ORDER BY `history_id` DESC '.$limitStr);
    }
   

    
}
